﻿ /*
 * ---------------------------------------------------
 * 作者: MTQ 
 * 日期： 2021/5/7 9:22:01 
 * ---------------------------------------------------
 */
using System;using System.Collections.Generic; 
using System.Linq; 
using System.Text; 
using System.Threading.Tasks; 
using MTQ.BLL.UploadExcel;                        
using System.Web;                                
using NPOI.SS.UserModel;                            
using MTQ.Model;             
using MTQ.BLL;             
using MTQ.IBLL;             
using MTQ.Common;             
using System.Data;               
namespace MTQ.BLL  
{  
 public partial  class Test_AutoCodeService : BaseService<Test_AutoCode>  
    {  
     public IQueryable<Test_AutoCode> LoadSearchData(Test_AutoCodeQuery query)  
         {  
           var temp = _DbSession.Test_AutoCodeRepository.LoadEntities(u => true);  
            //可根据所需查询条件自行添加  
         
                 if( !string.IsNullOrEmpty(query.T_Name)) 
           { 
               temp = temp.Where<Test_AutoCode>(u => u.T_Name.Contains(query.T_Name));   
           } 
 
         
         
         
         
         
         
                 if( !string.IsNullOrEmpty(query.T_Varchar)) 
           { 
               temp = temp.Where<Test_AutoCode>(u => u.T_Varchar.Contains(query.T_Varchar));   
           } 
 
                 if( !string.IsNullOrEmpty(query.T_Nvarchar)) 
           { 
               temp = temp.Where<Test_AutoCode>(u => u.T_Nvarchar.Contains(query.T_Nvarchar));   
           } 
 
                 if( !string.IsNullOrEmpty(query.T_Text)) 
           { 
               temp = temp.Where<Test_AutoCode>(u => u.T_Text.Contains(query.T_Text));   
           } 
 
                       query.Total = temp.Count(); 
           return temp.OrderBy(u => u.T_ID).Skip(query.PageSize * (query.PageIndex - 1)).Take(query.PageSize); 
       } 
       protected override IQueryable< Test_AutoCode> GetBootstrapQueryable<K>(K paramterquery) 
       { 
            Test_AutoCodeQuery query = paramterquery as  Test_AutoCodeQuery; 
           IQueryable< Test_AutoCode> temp = _DbSession. Test_AutoCodeRepository.LoadEntities(u => true); 
 
         
                 if( !string.IsNullOrEmpty(query.T_Name)) 
           { 
               temp = temp.Where<Test_AutoCode>(u => u.T_Name.Contains(query.T_Name));   
           } 
 
         
         
         
         
         
         
                 if( !string.IsNullOrEmpty(query.T_Varchar)) 
           { 
               temp = temp.Where<Test_AutoCode>(u => u.T_Varchar.Contains(query.T_Varchar));   
           } 
 
                 if( !string.IsNullOrEmpty(query.T_Nvarchar)) 
           { 
               temp = temp.Where<Test_AutoCode>(u => u.T_Nvarchar.Contains(query.T_Nvarchar));   
           } 
 
                 if( !string.IsNullOrEmpty(query.T_Text)) 
           { 
               temp = temp.Where<Test_AutoCode>(u => u.T_Text.Contains(query.T_Text));   
           } 
 
           return temp; 
       } 
        
    public IQueryable<Test_AutoCode> GetIQueryable(Test_AutoCodeQuery query)   
    {    
         return this.GetBootstrapQueryable<Test_AutoCodeQuery>(query);   
     }   
           public static bool ReadExcel(string filename, Guid userid, ref string msg)                                            
           {                                           
            DataTable dt = new DataTable();                                           
           string xlsPath = filename;                                           
            dt = GetDataNoPage("Test_AutoCode", "  1<>1   ", "T_ID").Tables[0];                                          
             string errorMessage = string.Empty;                                         
              var up = new UploadService().UploadXls(Test_AutoCodeService.ExcuteExcelSql, xlsPath, dt, out errorMessage);                                        
             if (!up)                                        
              { return false;}                                       
                                                     
                                                     
              DateTime datetime = System.DateTime.Now;                                       
              int i = 0;                                       
              foreach (DataRow dr in dt.Rows)                                       
              {                                       
                                                     
                                                     
              }                                       
                                                     
                                                     
                                                     
               return BLL.Test_AutoCodeService.DateTableSave(dt, "Test_AutoCode");                                      
            }                                       
                                                     
                                                     
          public static void ExcuteExcelSql(IWorkbook wookBook, DataTable dtvc, out string errormessage)                                           
          {                                           
          string ErrorMessage = string.Empty;                                           
            List<ExcelTableColumn> listExcelTableColumn = new List<ExcelTableColumn>();                                           
             listExcelTableColumn.Add(new ExcelTableColumn() { TableColumnName = "T_ID", ExcelColumnName = "T_ID", ExcelColumnIndex = -1 });                                          
             listExcelTableColumn.Add(new ExcelTableColumn() { TableColumnName = "T_Name", ExcelColumnName = "T_Name", ExcelColumnIndex = -1 });                                          
             listExcelTableColumn.Add(new ExcelTableColumn() { TableColumnName = "T_Int", ExcelColumnName = "T_Int", ExcelColumnIndex = -1 });                                          
             listExcelTableColumn.Add(new ExcelTableColumn() { TableColumnName = "T_IntNonull", ExcelColumnName = "T_IntNonull", ExcelColumnIndex = -1 });                                          
             listExcelTableColumn.Add(new ExcelTableColumn() { TableColumnName = "T_Date", ExcelColumnName = "T_Date", ExcelColumnIndex = -1 });                                          
             listExcelTableColumn.Add(new ExcelTableColumn() { TableColumnName = "T_DateNonull", ExcelColumnName = "T_DateNonull", ExcelColumnIndex = -1 });                                          
             listExcelTableColumn.Add(new ExcelTableColumn() { TableColumnName = "T_DateTime", ExcelColumnName = "T_DateTime", ExcelColumnIndex = -1 });                                          
             listExcelTableColumn.Add(new ExcelTableColumn() { TableColumnName = "T_DateTimeNonull", ExcelColumnName = "T_DateTimeNonull", ExcelColumnIndex = -1 });                                          
             listExcelTableColumn.Add(new ExcelTableColumn() { TableColumnName = "T_Varchar", ExcelColumnName = "T_Varchar", ExcelColumnIndex = -1 });                                          
             listExcelTableColumn.Add(new ExcelTableColumn() { TableColumnName = "T_Nvarchar", ExcelColumnName = "T_Nvarchar", ExcelColumnIndex = -1 });                                          
             listExcelTableColumn.Add(new ExcelTableColumn() { TableColumnName = "T_Text", ExcelColumnName = "T_Text", ExcelColumnIndex = -1 });                                          
                ISheet sheet = wookBook.GetSheet("sheet");                                      
                int columnindex = 0;                                      
                int firstRowNum = sheet.FirstRowNum + 1;                                      
                if (sheet.GetRow(0).GetCell(0).StringCellValue.StartsWith("上传项目数据"))                                      
                 {                                    
                   columnindex = 1;                                   
                   firstRowNum++;                                  
                 }                                    
                 for (int i = 0; i < 100; i++)                                     
                 {                                    
                    IRow row = sheet.GetRow(columnindex);                                 
                    if (row.GetCell(0) == null || row.GetCell(1) == null)                                 
                    {continue;}                                 
                     try{                                
                       string columnName = row.GetCell(i).StringCellValue;                              
                        if (string.IsNullOrEmpty(columnName))                              
                        {break;}                             
                        ExcelTableColumn etc = listExcelTableColumn.Where(x => x.ExcelColumnName == columnName).FirstOrDefault();                             
                         if (etc == null)                               
                        { continue;}                              
                        etc.ExcelColumnIndex = i;                              
                        if (listExcelTableColumn.Where(x => x.ExcelColumnIndex == -1).Count() == 0)                              
                        { break;}                              
                      }                               
                      catch                               
                       { break;}                                     
                 }                                    
                 if (listExcelTableColumn.Where(x => x.ExcelColumnIndex == -1).Count() > 0)                                     
                { errormessage = "列名不正确";}                                     
                else                                     
                 {                                    
                    int rowCount = sheet.LastRowNum;                                 
                     for (int j = firstRowNum; j <= sheet.LastRowNum; j++)                                 
                     {                                
                          DataRow dr = dtvc.NewRow();                            
                           IRow row = sheet.GetRow(j);                           
                          if (row == null){ continue;}                            
                           foreach (var item in listExcelTableColumn)                            
                           {                           
                                NPOI.XSSF.UserModel.XSSFCell cell = row.GetCell(item.ExcelColumnIndex) as NPOI.XSSF.UserModel.XSSFCell;                       
                                if (cell == null)                      
                                {                      
                                   dr[item.TableColumnName] = "";                   
                                 }                    
                                else                     
                                {                     
                                    if (cell.CellType == CellType.Numeric)                 
                                    {                 
                                        if (item.TableColumnType == TableColumnType.DateTime)              
                                         { dr[item.TableColumnName] = cell.DateCellValue;}            
                                        else             
                                         { dr[item.TableColumnName] = cell.GetRawValue();}            
                                     }                
                                     else if (cell.CellType == CellType.Blank)                 
                                     {}                
                                     else                
                                      { dr[item.TableColumnName] = cell.StringCellValue;}               
                                  }                   
                             }                        
                            dtvc.Rows.Add(dr);                          
                      }                               
                       errormessage = "";                               
                    }                                 
              }                                       
   } 
} 

